package com.sunwayworld.baseline.framework.override;

import com.sunwayworld.framework.beans.BeanPropertyDescriptor;
import com.sunwayworld.framework.exception.FileException;
import com.sunwayworld.framework.io.excel.SheetWriter;
import com.sunwayworld.framework.io.excel.impl.DefaultExcelWriter;
import com.sunwayworld.framework.io.excel.impl.DefaultSheetWriter;
import com.sunwayworld.framework.io.excel.support.SheetContext;
import com.sunwayworld.framework.io.excel.utils.ExcelUtils;
import com.sunwayworld.framework.io.file.FilePathDTO;
import com.sunwayworld.framework.io.file.FilePathManager;
import com.sunwayworld.framework.io.file.FileScope;
import com.sunwayworld.framework.support.PersistableHelper;
import com.sunwayworld.framework.support.domain.BaseData;
import com.sunwayworld.framework.utils.ConvertUtils;
import com.sunwayworld.framework.utils.FileUtils;
import com.sunwayworld.framework.utils.ObjectUtils;
import com.sunwayworld.framework.utils.StringUtils;
import org.apache.poi.ss.usermodel.*;

import java.io.*;
import java.nio.file.Files;
import java.nio.file.Path;
import java.text.DecimalFormat;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.*;
import java.util.stream.Collectors;

public abstract class LimsExcelHelper {
    private static final String SUP_MARK_START = "<sup>";
    private static final String SUP_MARK_END = "</sup>";
    private static final String SUB_MARK_START = "<sub>";
    private static final String SUB_MARK_END = "</sub>";
    private static final String BR_MARK_START = "<br>";
    private static final String BR_MARK_END = "</br>";
    private static final String NUMBER_FORMAT = "#.####################";

    public LimsExcelHelper() {
    }

    public static final <T> T getCellValue(final Cell cell, final Class<T> clazz) {
        Object cellValue = "";
        if (cell != null) {
            switch (cell.getCellType()) {
                case STRING:
                    cellValue = StringUtils.trim(cell.getRichStringCellValue().getString());
                    break;
                case NUMERIC:
                    short format = cell.getCellStyle().getDataFormat();
                    if (format != 14 && format != 22 && format != 31 && format != 57 && format != 58 && format != 20 && format != 32) {
                        cellValue = cell.getNumericCellValue();
                        DecimalFormat decimalFormat = new DecimalFormat("#.####################");
                        cellValue = decimalFormat.format(cell.getNumericCellValue());
                    } else {
                        cellValue = DateUtil.getJavaDate(cell.getNumericCellValue());
                    }
                    break;
                case BOOLEAN:
                    cellValue = ObjectUtils.toString(cell.getBooleanCellValue());
                    break;
                case FORMULA:
                    FormulaEvaluator evaluator = cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();
                    CellValue cv = evaluator.evaluate(cell);
                    switch (cv.getCellType()) {
                        case STRING:
                            cellValue = StringUtils.trim(cv.getStringValue());
                            return ConvertUtils.convert(cellValue, clazz);
                        case NUMERIC:
                            cellValue = cv.getNumberValue();
                            return ConvertUtils.convert(cellValue, clazz);
                        case BOOLEAN:
                            cellValue = ObjectUtils.toString(cv.getBooleanValue());
                            return ConvertUtils.convert(cellValue, clazz);
                        default:
                            cellValue = null;
                            return ConvertUtils.convert(cellValue, clazz);
                    }
                default:
                    cellValue = "";
            }
        }

        return ConvertUtils.convert(cellValue, clazz);
    }

    public static <T> T getCellValue(Cell cell, Class<?> clazz, String propertyName) {
        Object cellValue = "";
        BeanPropertyDescriptor propertyDescriptor = BeanPropertyDescriptor.of(clazz, propertyName);
        Class<?> propertyType = propertyDescriptor == null ? null : propertyDescriptor.getPropertyType();
        if (cell != null) {
            label28:
            switch (cell.getCellType()) {
                case STRING:
                    cellValue = StringUtils.trim(cell.getRichStringCellValue().getString());
                    break;
                case NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        cellValue = cell.getDateCellValue();
                    } else {
                        cellValue = cell.getNumericCellValue();
                    }
                    break;
                case BOOLEAN:
                    cellValue = ObjectUtils.toString(cell.getBooleanCellValue());
                    break;
                case FORMULA:
                    FormulaEvaluator evaluator = cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();
                    CellValue cv = evaluator.evaluate(cell);
                    switch (cv.getCellType()) {
                        case STRING:
                            cellValue = StringUtils.trim(cv.getStringValue());
                            break label28;
                        case NUMERIC:
                            cellValue = "" + cv.getNumberValue();
                            break label28;
                        case BOOLEAN:
                            cellValue = ObjectUtils.toString(cv.getBooleanValue());
                            break label28;
                        default:
                            cellValue = null;
                            break label28;
                    }
                default:
                    cellValue = "";
            }
        }

        propertyType = propertyType == null ? String.class : propertyType;
        T value = (T) ConvertUtils.convert(cellValue, propertyType);
        return value;
    }

    public static boolean canConvertCellValue(Cell cell, Class<?> targetType) {
        if (cell != null && targetType != null) {
            Object cellValue;
            switch (cell.getCellType()) {
                case STRING:
                    cellValue = StringUtils.trim(cell.getRichStringCellValue().getString());
                    break;
                case NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        cellValue = cell.getDateCellValue();
                    } else {
                        cellValue = cell.getNumericCellValue();
                    }
                    break;
                case BOOLEAN:
                    cellValue = ObjectUtils.toString(cell.getBooleanCellValue());
                    break;
                case FORMULA:
                    FormulaEvaluator evaluator = cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();
                    CellValue cv = evaluator.evaluate(cell);
                    switch (cv.getCellType()) {
                        case STRING:
                            cellValue = StringUtils.trim(cv.getStringValue());
                            return ConvertUtils.canConvert(cellValue, targetType);
                        case NUMERIC:
                            cellValue = "" + cv.getNumberValue();
                            return ConvertUtils.canConvert(cellValue, targetType);
                        case BOOLEAN:
                            cellValue = ObjectUtils.toString(cv.getBooleanValue());
                            return ConvertUtils.canConvert(cellValue, targetType);
                        default:
                            cellValue = null;
                            return ConvertUtils.canConvert(cellValue, targetType);
                    }
                default:
                    cellValue = "";
            }

            return ConvertUtils.canConvert(cellValue, targetType);
        } else {
            return true;
        }
    }

    public static void setCellStringValue(String text, Cell cell, Workbook wb) {
        if (text == null) {
            text = "";
        }

        text = text.replaceAll("</?p>", "");
        if (StringUtils.contains(text, "<br>")) {
            CellStyle cellStyle = cell.getCellStyle();
            cellStyle.setWrapText(true);
            text = StringUtils.replace(text, "<br>", "\n");
            text = StringUtils.replace(text, "</br>", "");
            cell.setCellStyle(cellStyle);
        }

        if (containCornerMark(text)) {
            RichTextString richTextString = replaceCornerMarkToText(text, wb);
            cell.setCellValue(richTextString);
        } else {
            cell.setCellValue(text);
        }

    }

    public static boolean containCornerMark(String text) {
        return StringUtils.contains(text, "<sub>") || StringUtils.contains(text, "<sup>");
    }

    public static RichTextString replaceCornerMarkToText(String text, Workbook wb) {
        RichTextString richText = wb.getCreationHelper().createRichTextString(text.replaceAll("</?su[b,p]>", ""));
        Font supFont = wb.createFont();
        supFont.setTypeOffset((short) 1);
        String supTmp = text.replaceAll("</?sub>", "");
        List<String> supList = StringUtils.substringsBetween(text, "<sup>", "</sup>");
        Iterator var6 = supList.iterator();

        String subTmp;
        while (var6.hasNext()) {
            subTmp = (String) var6.next();
            int index = supTmp.indexOf("<sup>" + subTmp + "</sup>");
            if (index != -1) {
                richText.applyFont(index, index + subTmp.length(), supFont);
                supTmp = supTmp.replaceFirst("<sup>", "").replaceFirst("</sup>", "");
            }
        }

        Font subFont = wb.createFont();
        subFont.setTypeOffset((short) 2);
        subTmp = text.replaceAll("</?sup>", "");
        List<String> subList = StringUtils.substringsBetween(text, "<sub>", "</sub>");
        Iterator var9 = subList.iterator();

        while (var9.hasNext()) {
            String sub = (String) var9.next();
            int index = subTmp.indexOf("<sub>" + sub + "</sub>");
            if (index != -1) {
                richText.applyFont(index, index + sub.length(), subFont);
                subTmp = subTmp.replaceFirst("<sub>", "").replaceFirst("</sub>", "");
            }
        }

        return richText;
    }

    public static <T extends BaseData> void writeAsExcel(File file, String name, SheetContext sheetContext, List<T> itemList) {
        SheetWriter writer = new DefaultSheetWriter(sheetContext, itemList);
        DefaultExcelWriter excelWriter = new DefaultExcelWriter(new SheetWriter[]{writer});

        try {
            FileOutputStream fos = new FileOutputStream(file);
            Throwable var7 = null;

            try {
                excelWriter.write(fos);
            } catch (Throwable var17) {
                var7 = var17;
                throw var17;
            } finally {
                if (fos != null) {
                    if (var7 != null) {
                        try {
                            fos.close();
                        } catch (Throwable var16) {
                            var7.addSuppressed(var16);
                        }
                    } else {
                        fos.close();
                    }
                }

            }

        } catch (IOException var19) {
            throw new FileException(var19);
        }
    }

    public static <T extends BaseData> FilePathDTO writeImportFailureListAsExcel(String filePath, List<T> itemList, int headerIndex, int validIndex, String errorFileName, boolean showImportDetailSum) {
        FilePathDTO fp = FilePathDTO.of(FileScope.temp.name(), LocalDateTime.now(), UUID.randomUUID().toString(), errorFileName);
        Path path = FilePathManager.getLocalPath(fp);
        FileUtils.makeDirs(path);

        int errorNum = 0;
        try {
            InputStream is = new BufferedInputStream(new FileInputStream(filePath));
            Throwable var8 = null;

            try {
                Workbook wb = WorkbookFactory.create(is);
                Throwable var10 = null;

                try {
                    Sheet sheet = wb.getSheetAt(0);
                    Row row = sheet.getRow(headerIndex);
                    int lastCellNum = row.getLastCellNum();
                    Cell errorCell = row.createCell(lastCellNum);
                    CellStyle cs = wb.createCellStyle();
//                    CellStyle cs = errorCell.getCellStyle();
                    cs.setBorderTop(BorderStyle.THIN);
                    cs.setBorderBottom(BorderStyle.THIN);
                    cs.setBorderLeft(BorderStyle.THIN);
                    cs.setBorderRight(BorderStyle.THIN);
                    cs.setFillForegroundColor(IndexedColors.RED.getIndex());
                    cs.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                    errorCell.setCellStyle(cs);
                    errorCell.setCellValue("错误信息");
                    int cellMaxWidth = ExcelUtils.getCellWidth("错误信息");

                    for (int i = 0; i < itemList.size(); ++i) {
                        T item = itemList.get(i);
                        row = sheet.getRow(i + validIndex);
                        errorCell = row.createCell(lastCellNum);
                        if (!StringUtils.isEmpty(item.getExt$().get("validateimpmsg"))) {
                            errorCell.setCellStyle(cs);
                            errorCell.setCellValue(item.getExt$().get("validateimpmsg"));
                            errorNum++;
                        } else {
                            errorCell.setCellStyle(cs);
                            errorCell.setCellValue("");
                        }
                        int length = ExcelUtils.getCellWidth(item.getExt$().get("validateimpmsg"));
                        if (cellMaxWidth < length) {
                            cellMaxWidth = length;
                        }
                    }

                    sheet.setColumnWidth(lastCellNum, Math.min(cellMaxWidth, 255 * 256));

                    if (showImportDetailSum) {
                        int numberOfRows = sheet.getPhysicalNumberOfRows();
                        row = sheet.getRow(numberOfRows);
                        if (row == null) {
                            row = sheet.createRow(numberOfRows);
                        }
                        errorCell = row.createCell(1);
                        errorCell.setCellStyle(cs);
                        String cellValue = "导入成功数：" + (itemList.size() - errorNum) + "; 导入失败数：" + errorNum;
                        errorCell.setCellValue(cellValue);
                    }

                    OutputStream fos = Files.newOutputStream(path);
                    Throwable var73 = null;

                    try {
                        wb.write(fos);
                        return fp;
                    } catch (Throwable var65) {
                        var73 = var65;
                        throw var65;
                    } finally {
                        if (fos != null) {
                            if (var73 != null) {
                                try {
                                    fos.close();
                                } catch (Throwable var64) {
                                    var73.addSuppressed(var64);
                                }
                            } else {
                                fos.close();
                            }
                        }

                    }
                } catch (Throwable var67) {
                    var10 = var67;
                    throw var67;
                } finally {
                    if (wb != null) {
                        if (var10 != null) {
                            try {
                                wb.close();
                            } catch (Throwable var63) {
                                var10.addSuppressed(var63);
                            }
                        } else {
                            wb.close();
                        }
                    }

                }
            } catch (Throwable var69) {
                var8 = var69;
                throw var69;
            } finally {
                if (is != null) {
                    if (var8 != null) {
                        try {
                            is.close();
                        } catch (Throwable var62) {
                            var8.addSuppressed(var62);
                        }
                    } else {
                        is.close();
                    }
                }

            }
        } catch (IOException var71) {
            throw new FileException(var71);
        }
    }

    public static <T extends BaseData> List<T> readDataFromExcel(String filePath, int headerRowIndex, int dataFromRowIndex, Class<T> clazz) {
        return readDataFromExcel(filePath, 0, headerRowIndex, dataFromRowIndex, clazz, (String) null);
    }

    public static <T extends BaseData> List<T> readDataFromExcel(String filePath, int headerRowIndex, int dataFromRowIndex, Class<T> clazz, String masterPropertyName) {
        return readDataFromExcel(filePath, 0, headerRowIndex, dataFromRowIndex, clazz, masterPropertyName);
    }

    public static <T extends BaseData> List<T> readDataFromExcel(String filePath, int sheetIndex, int headerRowIndex, int dataFromRowIndex, Class<T> clazz) {
        return readDataFromExcel(filePath, sheetIndex, headerRowIndex, dataFromRowIndex, clazz, (String) null);
    }

    public static <T extends BaseData> List<T> readDataFromExcel(String filePath, String sheetName, int headerRowIndex, int dataFromRowIndex, Class<T> clazz) {
        return extractDataFromExcel(filePath, sheetName, headerRowIndex, dataFromRowIndex, clazz, (String) null);
    }

    public static <T extends BaseData> List<T> readDataFromExcel(String filePath, int sheetIndex, int headerRowIndex, int dataFromRowIndex, Class<T> clazz, String masterPropertyName) {
        return extractDataFromExcel(filePath, sheetIndex, headerRowIndex, dataFromRowIndex, clazz, masterPropertyName);
    }

    private static <T extends BaseData> List<T> extractDataFromExcel(String filePath, Object sheetIndexOrSheetName, int headerRowIndex, int dataFromRowIndex, Class<T> clazz, String masterPropertyName) {
        try {
            Workbook wb = WorkbookFactory.create(new File(filePath));
            Throwable var7 = null;

            List var36;
            try {
                Sheet sheet = String.class.equals(sheetIndexOrSheetName.getClass()) ? wb.getSheet((String) sheetIndexOrSheetName) : wb.getSheetAt((Integer) sheetIndexOrSheetName);
                Row row = null;
                List<Map<String, Object>> result = new ArrayList();
                row = sheet.getRow(headerRowIndex);
                int columnQty = row.getPhysicalNumberOfCells();
                int offset = 0;
                if (!StringUtils.isEmpty(masterPropertyName)) {
                    offset = 1;
                }

                String[] columnNames = new String[columnQty];
                String[] showNames = new String[columnQty];

                int i;
                for (i = 1 - offset; i < columnNames.length; ++i) {
                    if (row.getCell(i).getCellComment() != null) {
                        columnNames[i] = row.getCell(i).getCellComment().getString().toString();
                        showNames[i] = row.getCell(i).getRichStringCellValue().getString();
                    } else if (row.getCell(i).getCellComment() == null) {
                        Row previousRow = sheet.getRow(headerRowIndex - 1);
                        if (previousRow != null && previousRow.getCell(i) != null && previousRow.getCell(i).getCellComment() != null) {
                            columnNames[i] = previousRow.getCell(i).getCellComment().getString().toString();
                            showNames[i] = previousRow.getCell(i).getRichStringCellValue().getString();
                        }
                    }
                }

                i = sheet.getPhysicalNumberOfRows();

                for (int rowNum = dataFromRowIndex; rowNum < i; ++rowNum) {
                    row = sheet.getRow(rowNum);
                    if (row != null) {
                        Map<String, Object> map = new HashMap();

                        for (int cellIndex = 1; cellIndex < columnNames.length; ++cellIndex) {
                            Cell cell = row.getCell(cellIndex);
                            if (columnNames[cellIndex] != null && columnNames[cellIndex].trim().length() > 0) {
                                BeanPropertyDescriptor propertyDescriptor = BeanPropertyDescriptor.of(clazz, columnNames[cellIndex].trim());
                                Class<?> targetType = propertyDescriptor == null ? String.class : propertyDescriptor.getPropertyType();
                                if (canConvertCellValue(cell, targetType)) {
                                    Object value = getCellValue(cell, clazz, columnNames[cellIndex].trim());
                                    if (!ObjectUtils.isEmpty(value)) {
                                        map.put(columnNames[cellIndex].trim(), value);
                                    }
                                } else {
                                    if (targetType.equals(LocalDateTime.class)) {
                                        updateValidationImpMsg(map, (showNames[cellIndex]) + "错误，时间格式为【yyyy-MM-dd HH:mm:ss】或按表头所备注的格式，请重新填写或检查时间是否存在；");
                                    } else if (targetType.equals(LocalDate.class)) {
                                        updateValidationImpMsg(map, (showNames[cellIndex]) + "错误，日期格式为【yyyy-MM-dd】或按表头所备注的格式，请重新填写或检查日期是否存在；");
                                    } else if (targetType.equals(Double.class) || targetType.equals(Long.class) || targetType.equals(Integer.class) || targetType.equals(Float.class)) {
                                        updateValidationImpMsg(map, (showNames[cellIndex]) + "错误，请填写数字；");
                                    } else {
                                        updateValidationImpMsg(map, (showNames[cellIndex]) + "错误，不能转换为【" + targetType.getSimpleName() + "】；");
                                    }
                                }
                            }
                        }

                        if (!StringUtils.isEmpty(masterPropertyName)) {
                            map.put(masterPropertyName, columnNames[0]);
                        }

                        result.add(map);
                    }
                }

                boolean isEmpty = true;

                for (i = result.size() - 1; i >= 0; --i) {
                    Iterator var37 = ((Map) result.get(i)).keySet().iterator();

                    while (var37.hasNext()) {
                        String key = (String) var37.next();
                        Object value = ((Map) result.get(i)).get(key);
                        if (!ObjectUtils.isEmpty(value)) {
                            isEmpty = false;
                        }
                    }

                    if (isEmpty) {
                        result.remove(i);
                    }
                }

                var36 = result.stream().map((m) -> PersistableHelper.mapToPersistable(m, clazz)).collect(Collectors.toList());
            } catch (Throwable var30) {
                var7 = var30;
                throw var30;
            } finally {
                if (wb != null) {
                    if (var7 != null) {
                        try {
                            wb.close();
                        } catch (Throwable var29) {
                            var7.addSuppressed(var29);
                        }
                    } else {
                        wb.close();
                    }
                }

            }

            return var36;
        } catch (IOException var32) {
            throw new FileException(var32);
        }
    }

    private static void updateValidationImpMsg(Map<String, Object> map, String msg) {
        String origMsg = (String) map.get("validateimpmsg");
        if (StringUtils.isEmpty(origMsg)) {
            map.put("validateimpmsg", msg);
        } else {
            map.put("validateimpmsg", origMsg + "；" + msg);
        }

    }
}
